package com.situ.dao;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

import com.situ.entily.Danju;
import com.situ.util.DBUtil;
import com.situ.vo.DanjuVO;

public class DanjuDao {

	public List<DanjuVO> all(int index, int limit, String keyword,Date keyword2) {
		String sql;
		if ((keyword == null || "".equals(keyword))&&(keyword2 == null)) {
			sql = "select * from danju inner join commodity on commodity.comm_id=danju.comm_id where dj_del=0 order by danju.dj_time desc limit ?,?";
		}else if((keyword != null || !"".equals(keyword))&&(keyword2 == null)) {
			
			sql = "select * from danju inner join commodity on commodity.comm_id=danju.comm_id where instr(commodity.comm_name,'" + keyword + "')>0 and dj_del=0 order by danju.dj_time desc limit ?,?";
		}else if((keyword == null || "".equals(keyword))&&(keyword2 != null)) {
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String keyword3=null;
			try {
				keyword3=sdf.format(keyword2);
			} catch (Exception e) {
				e.printStackTrace();
			}
			sql = "select * from danju inner join commodity on commodity.comm_id=danju.comm_id where instr(danju.dj_date,'"+keyword3+"')>0 and dj_del=0 order by danju.dj_time desc limit ?,?";
		} else {
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String keyword3=null;
			try {
				keyword3=sdf.format(keyword2);
			} catch (Exception e) {
				e.printStackTrace();
			}
			sql = "select * from danju inner join commodity on commodity.comm_id=danju.comm_id where instr(commodity.comm_name,'" + keyword + "')>0 and instr(danju.dj_date,'" + keyword3 + "')>0 and dj_del=0 order by danju.dj_time desc limit ?,?";
		}
		return DBUtil.executeDQL(sql, DanjuVO.class, index, limit);
		
	}

	public Long count(String keyword,Date keyword2) {
		String sql = "select count(1) from danju inner join commodity on commodity.comm_id=danju.comm_id where dj_del=0";
		if (keyword != null && !"".equals(keyword)&&(keyword2 == null)) {
			sql += " and instr(commodity.comm_name,'" + keyword + "')>0 ";
		}else if(keyword == null && "".equals(keyword)&&(keyword2 != null)) {
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String keyword3=null;
			try {
				keyword3=sdf.format(keyword2);
			} catch (Exception e) {
				e.printStackTrace();
			}
			sql+=" and instr(commodity.comm_name,'" + keyword3 + "')>0";
		}else if(keyword != null && !"".equals(keyword)&&(keyword2 != null)) {
			SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");
			String keyword3=null;
			try {
				keyword3=sdf.format(keyword2);
			} catch (Exception e) {
				e.printStackTrace();
			}
			sql+=" and instr(commodity.comm_name,'" + keyword + "')>0 and instr(danju.dj_date,'" + keyword3 + "')>0";
		}
		return DBUtil.executeCount(sql);
	}

	public void insert(Danju d) {


		//commodity.comm_name,commodity.comm_manufacturer,commodity.comm_marking,commodity.comm_specifications,dcomm_id,danju.dj_date,danju.dj_time,danju.dj_createby,danju.dj_unit,danju.dj_shipper,danju.dj_crk,danju.dj_quantity
		String sql = "insert into danju(comm_id,dj_date,dj_time,dj_createby,dj_unit,dj_shipper,dj_crk,dj_quantity) value(?,?,?,?,?,?,?,?)";				
		DBUtil.executeDML(sql, d.getCommId(),d.getDjDate(),d.getDjTime(),d.getDjCreateby(),d.getDjUnit(),d.getDjShipper(),d.getDjCrk(),d.getDjQuantity());
	}
	public void insert1(Danju d, BigDecimal bd){
		String sql1="update commodity set comm_quantity=? where comm_id =?";
		DBUtil.executeDML(sql1,bd,d.getCommId());
	}


	public void update(Danju d) {

	}	

	public void delete(Integer id) {
		//String sql = "delete from danju where dj_id=?";
		String sql = "update danju set dj_del=1 where dj_id=?";
		DBUtil.executeDML(sql, id);
		
	}

	public DanjuVO selectById(Integer id) {
		String sql = "select * from commodity inner join danju on commodity.comm_id=danju.comm_id where danju.dj_del=0 and danju.dj_id=?";

		List<DanjuVO> list = DBUtil.executeDQL(sql, DanjuVO.class, id);
		if (list.size() > 0) {
			return list.get(0);
		} else {
			return null;
		}
	}

	
	
	

}
